import sqlite3
import json
import uuid
from datetime import datetime
from typing import List, Optional, Dict, Any
from .models import ChartModel, DashboardModel, DashboardChartModel

class DashboardService:
    def __init__(self, db_path: str = "dashboard.db"):
        self.db_path = db_path
        self.init_database()
    
    def init_database(self):
        """初始化数据库表"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # 创建图表表
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS charts (
                id TEXT PRIMARY KEY,
                title TEXT NOT NULL,
                description TEXT,
                question TEXT NOT NULL,
                sql TEXT NOT NULL,
                chart_type TEXT NOT NULL,
                chart_config TEXT NOT NULL,
                data_config TEXT NOT NULL,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                tags TEXT DEFAULT '[]'
            )
        """)
        
        # 创建Dashboard表
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS dashboards (
                id TEXT PRIMARY KEY,
                name TEXT NOT NULL,
                description TEXT,
                layout_config TEXT NOT NULL,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                is_public INTEGER DEFAULT 0
            )
        """)
        
        # 创建Dashboard图表关联表
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS dashboard_charts (
                dashboard_id TEXT NOT NULL,
                chart_id TEXT NOT NULL,
                position TEXT NOT NULL,
                order_index INTEGER NOT NULL,
                PRIMARY KEY (dashboard_id, chart_id),
                FOREIGN KEY (dashboard_id) REFERENCES dashboards(id),
                FOREIGN KEY (chart_id) REFERENCES charts(id)
            )
        """)
        
        conn.commit()
        conn.close()
    
    def save_chart(self, chart_data: Dict[str, Any]) -> str:
        """保存图表"""
        chart_id = str(uuid.uuid4())
        now = datetime.now().isoformat()
        
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute("""
            INSERT INTO charts (id, title, description, question, sql, chart_type, 
                              chart_config, data_config, created_at, updated_at, tags)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            chart_id,
            chart_data['title'],
            chart_data.get('description'),
            chart_data['question'],
            chart_data['sql'],
            chart_data['chart_type'],
            json.dumps(chart_data['chart_config']),
            json.dumps(chart_data['data_config']),
            now,
            now,
            json.dumps(chart_data.get('tags', []))
        ))
        
        conn.commit()
        conn.close()
        return chart_id
    
    def get_charts(self, limit: int = 50) -> List[Dict[str, Any]]:
        """获取图表列表"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute("""
            SELECT * FROM charts ORDER BY created_at DESC LIMIT ?
        """, (limit,))
        
        charts = []
        for row in cursor.fetchall():
            chart = {
                'id': row[0],
                'title': row[1],
                'description': row[2],
                'question': row[3],
                'sql': row[4],
                'chart_type': row[5],
                'chart_config': json.loads(row[6]),
                'data_config': json.loads(row[7]),
                'created_at': row[8],
                'updated_at': row[9],
                'tags': json.loads(row[10])
            }
            charts.append(chart)
        
        conn.close()
        return charts
    
    def get_chart_by_id(self, chart_id: str) -> Optional[Dict[str, Any]]:
        """根据ID获取图表"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute("SELECT * FROM charts WHERE id = ?", (chart_id,))
        row = cursor.fetchone()
        
        if not row:
            conn.close()
            return None
        
        chart = {
            'id': row[0],
            'title': row[1],
            'description': row[2],
            'question': row[3],
            'sql': row[4],
            'chart_type': row[5],
            'chart_config': json.loads(row[6]),
            'data_config': json.loads(row[7]),
            'created_at': row[8],
            'updated_at': row[9],
            'tags': json.loads(row[10])
        }
        
        conn.close()
        return chart
    
    def create_dashboard(self, name: str, description: str = None) -> str:
        """创建Dashboard"""
        dashboard_id = str(uuid.uuid4())
        now = datetime.now().isoformat()
        
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute("""
            INSERT INTO dashboards (id, name, description, layout_config, 
                                  created_at, updated_at, is_public)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        """, (
            dashboard_id,
            name,
            description,
            json.dumps({'grid_size': 12, 'row_height': 300}),
            now,
            now,
            0
        ))
        
        conn.commit()
        conn.close()
        return dashboard_id
    
    def get_dashboards(self, limit: int = 50) -> List[Dict[str, Any]]:
        """获取Dashboard列表"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute("""
            SELECT * FROM dashboards ORDER BY created_at DESC LIMIT ?
        """, (limit,))
        
        dashboards = []
        for row in cursor.fetchall():
            dashboard_id = row[0]
            
            # 获取该dashboard的图表数量
            cursor.execute("""
                SELECT COUNT(*) FROM dashboard_charts WHERE dashboard_id = ?
            """, (dashboard_id,))
            chart_count = cursor.fetchone()[0]
            
            dashboard = {
                'id': dashboard_id,
                'name': row[1],
                'description': row[2],
                'layout_config': json.loads(row[3]),
                'created_at': row[4],
                'updated_at': row[5],
                'is_public': bool(row[6]),
                'charts': [{'count': chart_count}]  # 添加图表数量信息
            }
            dashboards.append(dashboard)
        
        conn.close()
        return dashboards
    
    def add_chart_to_dashboard(self, dashboard_id: str, chart_id: str, 
                              position: Dict[str, int], order: int = 0):
        """添加图表到Dashboard"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute("""
            INSERT OR REPLACE INTO dashboard_charts 
            (dashboard_id, chart_id, position, order_index)
            VALUES (?, ?, ?, ?)
        """, (
            dashboard_id,
            chart_id,
            json.dumps(position),
            order
        ))
        
        conn.commit()
        conn.close()
    
    def remove_chart_from_dashboard(self, dashboard_id: str, chart_id: str):
        """从Dashboard中移除图表"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute("""
            DELETE FROM dashboard_charts 
            WHERE dashboard_id = ? AND chart_id = ?
        """, (dashboard_id, chart_id))
        
        conn.commit()
        conn.close()
    
    def update_chart_position(self, dashboard_id: str, chart_id: str, position: Dict[str, int]):
        """更新图表在Dashboard中的位置"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute("""
            UPDATE dashboard_charts 
            SET position = ?
            WHERE dashboard_id = ? AND chart_id = ?
        """, (json.dumps(position), dashboard_id, chart_id))
        
        conn.commit()
        conn.close()
    
    def get_dashboard_with_charts(self, dashboard_id: str) -> Optional[Dict[str, Any]]:
        """获取Dashboard及其图表"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # 获取Dashboard信息
        cursor.execute("SELECT * FROM dashboards WHERE id = ?", (dashboard_id,))
        dashboard_row = cursor.fetchone()
        
        if not dashboard_row:
            conn.close()
            return None
        
        # 获取Dashboard中的图表
        cursor.execute("""
            SELECT c.*, dc.position, dc.order_index
            FROM charts c
            JOIN dashboard_charts dc ON c.id = dc.chart_id
            WHERE dc.dashboard_id = ?
            ORDER BY dc.order_index
        """, (dashboard_id,))
        
        charts = []
        for row in cursor.fetchall():
            chart = {
                'id': row[0],
                'title': row[1],
                'description': row[2],
                'question': row[3],
                'sql': row[4],
                'chart_type': row[5],
                'chart_config': json.loads(row[6]),
                'data_config': json.loads(row[7]),
                'created_at': row[8],
                'updated_at': row[9],
                'tags': json.loads(row[10]),
                'position': json.loads(row[11]),
                'order': row[12]
            }
            charts.append(chart)
        
        dashboard = {
            'id': dashboard_row[0],
            'name': dashboard_row[1],
            'description': dashboard_row[2],
            'layout_config': json.loads(dashboard_row[3]),
            'created_at': dashboard_row[4],
            'updated_at': dashboard_row[5],
            'is_public': bool(dashboard_row[6]),
            'charts': charts
        }
        
        conn.close()
        return dashboard
    
    def delete_chart(self, chart_id: str) -> bool:
        """删除图表"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # 先删除Dashboard关联
        cursor.execute("DELETE FROM dashboard_charts WHERE chart_id = ?", (chart_id,))
        
        # 再删除图表
        cursor.execute("DELETE FROM charts WHERE id = ?", (chart_id,))
        
        affected_rows = cursor.rowcount
        conn.commit()
        conn.close()
        
        return affected_rows > 0
    
    def update_dashboard_layout(self, dashboard_id: str, layout: List[Dict[str, Any]]) -> bool:
        """更新Dashboard布局"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        now = datetime.now().isoformat()
        
        try:
            # 更新每个图表的位置信息
            for chart_layout in layout:
                chart_id = chart_layout.get('chart_id')
                position = {
                    'x': chart_layout.get('position_x'),
                    'y': chart_layout.get('position_y'),
                    'width': chart_layout.get('width'),
                    'height': chart_layout.get('height')
                }
                
                cursor.execute("""
                    UPDATE dashboard_charts 
                    SET position = ?
                    WHERE dashboard_id = ? AND chart_id = ?
                """, (json.dumps(position), dashboard_id, chart_id))
            
            # 更新仪表板的更新时间
            cursor.execute("""
                UPDATE dashboards 
                SET updated_at = ?
                WHERE id = ?
            """, (now, dashboard_id))
            
            conn.commit()
            return True
            
        except Exception as e:
            conn.rollback()
            return False
        finally:
             conn.close()
    
    def update_chart_title(self, chart_id: str, title: str) -> bool:
        """更新图表标题"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        now = datetime.now().isoformat()
        
        cursor.execute("""
            UPDATE charts 
            SET title = ?, updated_at = ?
            WHERE id = ?
        """, (title, now, chart_id))
        
        affected_rows = cursor.rowcount
        conn.commit()
        conn.close()
        
        return affected_rows > 0
     
    def update_dashboard(self, dashboard_id: str, name: str, description: str = None) -> bool:
        """更新Dashboard"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        now = datetime.now().isoformat()
        
        cursor.execute("""
            UPDATE dashboards 
            SET name = ?, description = ?, updated_at = ?
            WHERE id = ?
        """, (name, description, now, dashboard_id))
        
        affected_rows = cursor.rowcount
        conn.commit()
        conn.close()
        
        return affected_rows > 0
    
    def update_chart(self, chart_id: str, update_data: Dict[str, Any]) -> bool:
        """更新图表"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        now = datetime.now().isoformat()
        
        # 构建动态更新SQL
        set_clauses = []
        values = []
        
        for key, value in update_data.items():
            if key in ['title', 'description', 'question', 'sql', 'chart_type']:
                set_clauses.append(f"{key} = ?")
                values.append(value)
            elif key in ['chart_config', 'data_config']:
                set_clauses.append(f"{key} = ?")
                values.append(json.dumps(value) if isinstance(value, (dict, list)) else value)
        
        if not set_clauses:
            conn.close()
            return False
        
        # 添加更新时间
        set_clauses.append("updated_at = ?")
        values.append(now)
        values.append(chart_id)
        
        sql = f"UPDATE charts SET {', '.join(set_clauses)} WHERE id = ?"
        
        cursor.execute(sql, values)
        
        affected_rows = cursor.rowcount
        conn.commit()
        conn.close()
        
        return affected_rows > 0
    
    def delete_dashboard(self, dashboard_id: str) -> bool:
        """删除Dashboard"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # 先删除图表关联
        cursor.execute("DELETE FROM dashboard_charts WHERE dashboard_id = ?", (dashboard_id,))
        
        # 再删除Dashboard
        cursor.execute("DELETE FROM dashboards WHERE id = ?", (dashboard_id,))
        
        affected_rows = cursor.rowcount
        conn.commit()
        conn.close()
        
        return affected_rows > 0